package dyl.easycode.service;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;

import org.apache.commons.collections.map.CaseInsensitiveMap;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import dyl.common.util.DylSqlUtil;
import dyl.common.util.JdbcTemplateUtil;
import dyl.common.util.Page;
import dyl.easycode.bean.Column;
import dyl.easycode.bean.EasyCode;
import dyl.easycode.bean.Table;
/**

 * @author Dyl
 * 2017-05-31 15:06:00
 */
@Service
@Transactional
public class EasyCodeServiceImpl {
	@Resource
	private JdbcTemplateUtil jdbcTemplate;
	/**
	 * 说明：分页查询表v_easy_code记录封装成List集合
	 * @return List<EasyCode
	 */
	public List<EasyCode>  findEasyCodeList(Page page,EasyCode easyCode) throws Exception{
		String sql = "";
		if(DylSqlUtil.isORACLE){
			sql ="select t.TABLE_NAME as name from user_tables t union all select t.VIEW_NAME as name from user_views t";
		}else if(DylSqlUtil.isMYSQL){
			sql = "select TABLE_NAME as name from information_schema.tables where TABLE_SCHEMA='dyl-sys'";
		}
		List<Object> con = new ArrayList<Object>();
		if(StringUtils.isNotEmpty(easyCode.getName())){
			sql+=" and t.name like ?";
			con.add("%"+easyCode.getName()+"%");
		}
		List<EasyCode> easyCodeList =  jdbcTemplate.queryForListBeanByPage(sql, con, EasyCode.class, page);
		
		return easyCodeList;
	}


	/**
	 * 获取某张表的所以列名，并存入List中
	 * @param tableName 表名
	 * @return
	 * @throws SQLException
	 */
	public  Table getColumnList(EasyCode easyCode) throws SQLException {
		Table table = new Table(easyCode.getName());
		table.setPrimaryKey(new Column(easyCode.getpKey()));
		Connection conn = jdbcTemplate.getDataSource().getConnection();
		Map<String,String> remarksMap = getRemarks(conn, table.getTableName());
		//预编译的 SQL 语句
		PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM " + table.getTableName());
		//获取结果集
		ResultSet rs = pstmt.executeQuery();
		//获取所有字段名
		ResultSetMetaData rsmd = rs.getMetaData(); 
		if (rsmd != null) {
			int count = rsmd.getColumnCount();
			for (int i = 1; i <= count; i++) {
				//if(colums!=null&&colums.indexOf(rsmd.getColumnName(i).toLowerCase())==-1)continue;
				Column c = new Column(rsmd.getColumnName(i).toLowerCase());
				c.setJdbcType(rsmd.getColumnType(i));
				swiftJavaType(c,rsmd.getColumnClassName(i));
				c.setNullable(rsmd.isNullable(i)==0?false:true);
				c.setAutoincrement(rsmd.isAutoIncrement(i)); 
				c.setColumnType(rsmd.getColumnTypeName(i));
				c.setSize(rsmd.getColumnDisplaySize(i));
				c.setRemarks(remarksMap.get(rsmd.getColumnName(i).toUpperCase()));
				if(isPrimaryKey(conn,table.getTableName(),rsmd.getColumnName(i)) || table.getPrimaryKey().getColumnName().equals(rsmd.getColumnName(i).toLowerCase())){
					c.setPrimaryKey(true);
					table.addPrimaryKey(c);
					table.setPrimaryKey(c);
				}else{
					table.addBaseColumn(c);
				}
			}
		}
		table.setImportList(getImport(table.getColumns()));
		return table;
	}
	private  Set<String> getImport(List<Column> colums){
		Set<String> importSet = new HashSet<String>();
		for (Column c :colums){
			try {
				c.setJavaTypeInPackAge(c.getJavaType());
				String import_=c.getJavaType().substring(0,c.getJavaType().lastIndexOf("."));
				if(!"java.lang".equals(import_))importSet.add(c.getJavaType());
				c.setJavaType(c.getJavaType().substring(c.getJavaType().lastIndexOf(".")+1));
			} catch (Exception e) {
			}
		}
		return importSet;
	}
	private  void swiftJavaType(Column c,String javaType){
		String changeToString="oracle.sql.CLOB";
		//String changeToInteger="java.math.BigDecimal";
		if(changeToString.indexOf(javaType)!=-1){
			c.setJavaType("java.lang.String");
		}else if("java.sql.Timestamp".equals(javaType)){
			c.setJavaType("java.util.Date");
		}else{
			c.setJavaType(javaType);
		}
	}
	/**
	 * 判断某表中的字段是否是主键
	 * @param tableName 表面
	 * @param columnName 列名
	 * @return 返回 true 为是主键 ，返回false则不是
	 * @throws SQLException
	 */
	public  boolean isPrimaryKey(Connection conn,String tableName,String columnName) throws SQLException {
		boolean flag = false ;
		DatabaseMetaData dbMeta = conn.getMetaData();
		ResultSet primaryKey = dbMeta.getPrimaryKeys(null, null, tableName.toUpperCase());
		while (primaryKey.next()) {
			/*System.out.print("表名:" + primaryKey.getString(3));
			System.out.print("  列名:" + primaryKey.getString(4));
			System.out.println("  主键名:" + primaryKey.getString(6));
			System.out.println(primaryKey.getString(4));*/
			if(columnName.equals(primaryKey.getString(4))){
				flag =  true;
				break ;
			}
		}
		return flag;
	}
	/**
	 * oracle查询数据库注释
	 * @param conn
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public  Map<String,String> getRemarksByOracle(Connection conn,String tableName)  throws SQLException{
		Map<String,String> hashMap = new HashMap<String, String>();
		//预编译的 SQL 语句
		PreparedStatement pstmt = conn.prepareStatement("select * from user_col_comments where table_name='"+tableName.toUpperCase()+"'");
		//获取结果集
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()) {
			if(rs.getString(3)!=null)
			hashMap.put(rs.getString(2), rs.getString(3).replace("\n", " "));
		} 
		return hashMap;
	}
	/**
	 * mysql查询数据库注释
	 * @param conn
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public  Map<String,String> getRemarksByMysql(Connection conn,String tableName)  throws SQLException{
		Map<String,String> hashMap = new CaseInsensitiveMap();
		//预编译的 SQL 语句
		PreparedStatement pstmt = conn.prepareStatement("show full columns from "+tableName);
		//获取结果集
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()) {
			if(rs.getString(1)!=null)
			hashMap.put(rs.getString(1), rs.getString(9).replace("\n", " "));
		} 
		return hashMap;
	}
	public  Map<String,String> getRemarks(Connection conn,String tableName)  throws SQLException{
		Map<String,String> hashMap = new CaseInsensitiveMap();
		if(DylSqlUtil.isMYSQL){
			return getRemarksByMysql(conn, tableName);
		}else if(DylSqlUtil.isORACLE){
			return getRemarksByOracle(conn, tableName);
		}
		return hashMap;
	}
}
